USE [ServerMonitor]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AllServerJobsMonitor]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AllServerJobsMonitor]
GO
USE [ServerMonitor]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AllServerJobsMonitor]
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @Key NVARCHAR(256) = 'lenovo,2008'
  DECLARE ServerCur CURSOR LOCAL LOCAL READ_ONLY
  FOR
    SELECT [Id],[IP],
    CAST(DecryptByPassphrase(@Key,[UserName]) AS NVARCHAR(256)),
    CAST(DecryptByPassphrase(@Key,[Password]) AS NVARCHAR(256))
    FROM [ServerMonitor].[dbo].[Server]
    ORDER BY [Id]
  DECLARE @ServerId INT, @IP VARCHAR(50), @User SYSNAME, @PW SYSNAME
  OPEN ServerCur
  FETCH NEXT FROM ServerCur INTO @ServerId,@IP,@User,@PW
  WHILE @@FETCH_STATUS = 0
  BEGIN    
    DECLARE @JobTable TABLE(Id BIGINT,JobName NVARCHAR(256),Status BIT,ExecutionTime DATETIME,NextRunTime DATETIME,
    DurationTime INT,UpdateTime DATETIME)
    DECLARE @query NVARCHAR(MAX) = REPLACE('
    SELECT [Id],[JobName],[Status],[ExecutionTime],[NextRunTime],[DurationTime],[UpdateTime]
    FROM OPENDATASOURCE(''SQLOLEDB'',''DATA SOURCE=#SourceIP#;USER ID=#User#;PASSWORD=#PW#'').
    [ServerMonitor].[dbo].[JobStatus]
    WHERE DATEDIFF(DAY,[UpdateTime],GETDATE()) <= 7','#SourceIP#',@IP)
    SET @query = REPLACE(@query,'#User#',@User)
    SET @query = REPLACE(@query,'#PW#',@PW)
    INSERT INTO @JobTable
    EXEC SP_EXECUTESQL @query  
  
    MERGE INTO [ServerMonitor].[dbo].[AllServerJobStatus] AS Target
    USING (SELECT [Id],@ServerId AS ServerId,[JobName],[Status],[ExecutionTime],[NextRunTime],[DurationTime],[UpdateTime] FROM @JobTable)
    AS Source([Id],[ServerId],[JobName],[Status],[ExecutionTime],[NextRunTime],[DurationTime],[UpdateTime])
    ON Target.[Id] = Source.[Id] AND Target.[ServerId] = Source.[ServerId]
    WHEN MATCHED THEN 
    UPDATE SET [JobName] = Source.[JobName],[Status] = Source.[Status],[ExecutionTime] = Source.[ExecutionTime],
    [NextRunTime] = Source.[NextRunTime],[DurationTime] = Source.[DurationTime],[UpdateTime] = Source.[UpdateTime]
    WHEN NOT MATCHED THEN
    INSERT ([Id],[ServerId],[JobName],[Status],[ExecutionTime],[NextRunTime],[DurationTime],[UpdateTime])
    VALUES(Source.[Id],Source.[ServerId],Source.[JobName],Source.[Status],Source.[ExecutionTime],
    Source.[NextRunTime],Source.[DurationTime],Source.[UpdateTime]);
    
    DECLARE @MinId INT, @MaxId INT
    SELECT @MinId = MIN(Id),@MaxId = MAX(Id) FROM @JobTable
    
    DELETE FROM @JobTable  
    
    DECLARE @StepTable TABLE(Id BIGINT,JobStatusId BIGINT,name NVARCHAR(256),StepId INT,ExecutionTime DATETIME,
    DurationTime INT,Status BIT,Message NVARCHAR(3000),UpdateTime DATETIME)
    SET @query = REPLACE('
    SELECT [Id],[JobStatusId],[Name],[StepId],[ExecutionTime],[DurationTime],[Status],[Message],[UpdateTime]
    FROM OPENDATASOURCE(''SQLOLEDB'',''DATA SOURCE=#SourceIP#;USER ID=#User#;PASSWORD=#PW#'').
    [ServerMonitor].[dbo].[JobStepStatus]
    WHERE [JobStatusId] >= #MinId# AND [JobStatusId] <= #MaxId#','#SourceIP#',@IP)
    SET @query = REPLACE(@query,'#User#',@User)
    SET @query = REPLACE(@query,'#PW#',@PW)
    SET @query = REPLACE(@query,'#MinId#',CONVERT(VARCHAR(9),@MinId))
    SET @query = REPLACE(@query,'#MaxId#',CONVERT(VARCHAR(9),@MaxId))
    INSERT INTO @StepTable
    EXEC SP_EXECUTESQL @query  
    
    MERGE INTO [ServerMonitor].[dbo].[AllServerJobStepStatus] AS Target
    USING (SELECT [Id],@ServerId AS ServerId,[JobStatusId],[Name],[StepId],[ExecutionTime],[DurationTime],[Status],[Message],[UpdateTime] FROM @StepTable)
    AS Source([Id],[ServerId],[JobStatusId],[Name],[StepId],[ExecutionTime],[DurationTime],[Status],[Message],[UpdateTime])
    ON Target.[Id] = Source.[Id] AND Target.[ServerId] = Source.[ServerId] AND Target.[JobStatusId] = Source.[JobStatusId] AND Target.[StepId] = Source.[StepId]
    WHEN MATCHED THEN 
    UPDATE SET [StepId] = Source.[StepId],[Name] = Source.[Name],[ExecutionTime] = Source.[ExecutionTime],
    [DurationTime] = Source.[DurationTime],[Status] = Source.[Status],[Message] = Source.[Message],[UpdateTime] = Source.[UpdateTime]
    WHEN NOT MATCHED THEN
    INSERT ([Id],[ServerId],[JobStatusId],[Name],[StepId],[ExecutionTime],[DurationTime],[Status],[Message],[UpdateTime])
    VALUES(Source.[Id],Source.[ServerId],Source.[JobStatusId],Source.[Name],Source.[StepId],
    Source.[ExecutionTime],Source.[DurationTime],Source.[Status],Source.[Message],Source.[UpdateTime]);   
    
    DELETE FROM @StepTable
  
    FETCH NEXT FROM ServerCur INTO @ServerId,@IP,@User,@PW
  END
  CLOSE ServerCur
  DEALLOCATE ServerCur  
  
  SET NOCOUNT OFF 
END
GO